IF OBJECT_ID('ssf.utl_string_split') IS NOT NULL
	DROP FUNCTION ssf.utl_string_split
GO

CREATE FUNCTION ssf.utl_string_split (
	@string VARCHAR(MAX)
	, @separator VARCHAR(64)
)
RETURNS TABLE
AS
RETURN (
	WITH string AS (
		SELECT @string AS [str]
			, DATALENGTH(@string) AS str_len
			, @separator AS sep
			, DATALENGTH(@separator) AS sep_len
			, CAST(1 AS BIGINT) AS st_pos
			, CHARINDEX(@separator, @string) AS sep_pos
		WHERE @string IS NOT NULL
		AND @separator IS NOT NULL
	)
	, split AS (
		SELECT CAST(1 AS BIGINT) AS str_ord
			, SUBSTRING([str], st_pos, CASE WHEN sep_pos > 0 THEN sep_pos - 1 ELSE str_len END) AS [str]
			, CASE WHEN sep_pos = 0 THEN str_len + 2 ELSE sep_pos + sep_len END AS st_pos -- use + 2 to exit recursion
			, CHARINDEX(sep, [str], sep_pos + sep_len) AS sep_pos
		FROM string
		UNION ALL
		SELECT sp.str_ord + 1
			, SUBSTRING([str].[str], sp.st_pos, CASE WHEN sp.sep_pos > 0 THEN sp.sep_pos - sp.st_pos ELSE [str].str_len END) AS [str]
			, CASE WHEN sp.sep_pos = 0 THEN [str].str_len + 2 ELSE sp.sep_pos + [str].sep_len END AS st_pos -- use + 2 to exit recursion
			, CHARINDEX([str].sep, [str].[str], sp.sep_pos + [str].sep_len) AS sep_pos
		FROM split sp
		CROSS JOIN string [str]
		WHERE sp.st_pos <= [str].str_len + 1 -- need to allow + 1 to handle str ending with sep
	)
	SELECT str_ord AS string_order
		, [str] AS string
	FROM split
);
GO
